package com.bdqn.tour.dao.impl;



import com.bdqn.tour.dao.BaseDao;
import com.bdqn.tour.dao.HzyTravelersDao;
import com.bdqn.tour.pojo.HzyTraveler_documents;
import com.bdqn.tour.pojo.HzyTravelers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class HzyTravelersDaoImpl extends BaseDao implements HzyTravelersDao {
    @Override
    public int addTraveler(HzyTravelers travelers) {
        int generatedId = 0;
        conn = getConnection();
        String sql = "INSERT INTO travelers (chinese_name, surname, given_name, gender, nationality, birth_date, phone, email, is_default) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            // 设置返回生成的主键
            psmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            psmt.setString(1, travelers.getChineseName());
            psmt.setString(2, travelers.getSurname());
            psmt.setString(3, travelers.getGivenName());
            psmt.setString(4, travelers.getGender());
            psmt.setString(5, travelers.getNationality());
            psmt.setString(6, travelers.getBirthDate());
            psmt.setString(7, travelers.getPhone());
            psmt.setString(8, travelers.getEmail());
            psmt.setBoolean(9, travelers.isDefault());

            int result = psmt.executeUpdate();

            if (result > 0) {
                // 获取生成的主键
                ResultSet rs = psmt.getGeneratedKeys();
                if (rs.next()) {
                    generatedId = rs.getInt(1);
                    travelers.setId(generatedId); // 设置生成的ID
                }
                rs.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
        return generatedId;
    }

    @Override
    public void addTraveler_documents(HzyTraveler_documents traveler_documents) {
        conn = getConnection();
        String sql = "INSERT INTO traveler_documents (traveler_id, document_type, document_number,expiry_date) VALUES (?, ?, ?,?)";
        try {
            executeUpdate(sql, traveler_documents.getTraveler_id(), traveler_documents.getDocument_type(), traveler_documents.getDocument_number(), traveler_documents.getExpiry_date());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
    }

    @Override
    public List<HzyTravelers> getTravelersList(String searchName) {
        List<HzyTravelers> travelersList = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT t.*, td.id as doc_id, td.document_type, td.document_number, td.expiry_date " +
                        "FROM travelers t " +
                        "LEFT JOIN traveler_documents td ON t.id = td.traveler_id"
        );
        // 只有当搜索名不为空时才添加 WHERE 子句
        if (searchName != null && !searchName.isEmpty()) {
            sql.append(" WHERE CONCAT(IFNULL(t.chinese_name,''), ' ', " +
                    "IFNULL(t.surname,''), ' ', " +
                    "IFNULL(t.given_name,'')) LIKE ?");
        }
        sql.append(" ORDER BY t.is_default DESC, t.id DESC");
        try {
            conn = getConnection();


            psmt = conn.prepareStatement(sql.toString());
            if (searchName != null && !searchName.isEmpty()) {
                String searchPattern = "%" + searchName + "%";
                psmt.setString(1, searchPattern);

            }

            rs = psmt.executeQuery();

            HzyTravelers currentTraveler = null;
            int currentTravelerId = -1;
            while (rs.next()) {
                int travelerId = rs.getInt("id");

                if (currentTravelerId != travelerId) {
                    currentTraveler = new HzyTravelers();
                    currentTraveler.setId(travelerId);
                    currentTraveler.setChineseName(rs.getString("chinese_name"));
                    currentTraveler.setSurname(rs.getString("surname"));
                    currentTraveler.setGivenName(rs.getString("given_name"));
                    currentTraveler.setGender(rs.getString("gender"));
                    currentTraveler.setNationality(rs.getString("nationality"));
                    currentTraveler.setBirthDate(rs.getString("birth_date"));
                    currentTraveler.setPhone(rs.getString("phone"));
                    currentTraveler.setEmail(rs.getString("email"));
                    currentTraveler.setDefault(rs.getBoolean("is_default"));
                    currentTraveler.setDocuments(new ArrayList<>());
                    travelersList.add(currentTraveler);
                    currentTravelerId = travelerId;

                }
                // 处理证件信息
                if (rs.getObject("doc_id") != null) {
                    HzyTraveler_documents doc = new HzyTraveler_documents();
                    doc.setId(rs.getInt("doc_id"));
                    doc.setTraveler_id(travelerId);
                    doc.setDocument_type(rs.getString("document_type"));
                    doc.setDocument_number(rs.getString("document_number"));
                    doc.setExpiry_date(rs.getString("expiry_date"));
                    currentTraveler.getDocuments().add(doc);
                }
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
        return travelersList;
    }

    @Override
    public boolean deleteTraveler(int id) {
        conn = getConnection();
        try {
            // 先删除关联的证件信息
            String deleteDocsSql = "DELETE FROM traveler_documents WHERE traveler_id = ?";
            psmt = conn.prepareStatement(deleteDocsSql);
            psmt.setInt(1, id);
            psmt.executeUpdate();

            // 再删除旅客信息
            String deleteTravelerSql = "DELETE FROM travelers WHERE id = ?";
            psmt = conn.prepareStatement(deleteTravelerSql);
            psmt.setInt(1, id);
            return psmt.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
    }

    @Override
    public boolean setDefaultTraveler(int id) {
        conn = getConnection();
        try {
            // 先将所有旅客设置为非默认
            String resetSql = "UPDATE travelers SET is_default = 0";
            psmt = conn.prepareStatement(resetSql);
            psmt.executeUpdate();

            // 将指定旅客设置为默认
            String setSql = "UPDATE travelers SET is_default = 1 WHERE id = ?";
            psmt = conn.prepareStatement(setSql);
            psmt.setInt(1, id);
            return psmt.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
    }

    @Override
    public HzyTravelers getTravelerById(int id) {
        HzyTravelers traveler = null;
        String sql = "SELECT t.*, td.id as doc_id, td.document_type, td.document_number, td.expiry_date " +
                "FROM travelers t " +
                "LEFT JOIN traveler_documents td ON t.id = td.traveler_id " +
                "WHERE t.id = ?";

        try {
            conn = getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, id);
            rs = psmt.executeQuery();

            while (rs.next()) {
                if (traveler == null) {
                    traveler = new HzyTravelers();
                    traveler.setId(rs.getInt("id"));
                    traveler.setChineseName(rs.getString("chinese_name"));
                    traveler.setSurname(rs.getString("surname"));
                    traveler.setGivenName(rs.getString("given_name"));
                    traveler.setGender(rs.getString("gender"));
                    traveler.setNationality(rs.getString("nationality"));
                    traveler.setBirthDate(rs.getString("birth_date"));
                    traveler.setPhone(rs.getString("phone"));
                    traveler.setEmail(rs.getString("email"));
                    traveler.setDefault(rs.getBoolean("is_default"));
                    traveler.setDocuments(new ArrayList<>());
                }

                // 添加证件信息
                if (rs.getObject("doc_id") != null) {
                    HzyTraveler_documents doc = new HzyTraveler_documents();
                    doc.setId(rs.getInt("doc_id"));
                    doc.setTraveler_id(id);
                    doc.setDocument_type(rs.getString("document_type"));
                    doc.setDocument_number(rs.getString("document_number"));
                    doc.setExpiry_date(rs.getString("expiry_date"));
                    traveler.getDocuments().add(doc);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
        return traveler;
    }

    @Override
    public boolean updateTraveler(HzyTravelers travelers) {
        conn = getConnection();
        String sql = "UPDATE travelers SET chinese_name = ?, surname = ?, given_name = ?, gender = ?, nationality = ?, birth_date = ?, phone = ?, email = ?, is_default = ? WHERE id = ?";
        try {
            return executeUpdate(sql, travelers.getChineseName(), travelers.getSurname(), travelers.getGivenName(), travelers.getGender(), travelers.getNationality(), travelers.getBirthDate(), travelers.getPhone(), travelers.getEmail(), travelers.isDefault(), travelers.getId()) > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn, psmt, rs);
        }
    }

    @Override
    public boolean deleteAllDocuments(int travelerId) {
        conn = getConnection();
        String sql = "DELETE FROM traveler_documents WHERE traveler_id = ?";
        try {
            return executeUpdate(sql, travelerId) >= 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
    }
}
